use axum::{extract::Query, Json};
use com::{ chrono::{self, now_format}, code, query, sea_orm::{bus, connect_db, send_sql_ok } };
use sea_orm::{query::*, DbBackend, DbErr };

use super::master::table_exist;

pub async fn create_table(Json(body):Json<JsonValue>) -> Result<Json<JsonValue>, Json<JsonValue>>{
    let dbname = query::str(&body,"database")?;
    let table_name = query::str(&body,"table_name")?;
    let conn = connect_db(&dbname).await?;
    let conn = bus::begin(conn).await?;
    let stmt1 = Statement::from_string(
        DbBackend::MySql, 
        format!(r#"
            create table if not exists {} (
                id int not null auto_increment primary key comment 'ID',
                mark varchar(30) comment '备注',
                create_user	int not null comment '创建人',
                update_user	int comment '创建修改',
                create_time	timestamp default current_timestamp comment '创建时间',
                update_time	timestamp default current_timestamp on update current_timestamp comment '更新时间'
            )
        "#, table_name)
    );
    let stmt2 = Statement::from_string(
        DbBackend::MySql, 
        format!("insert into seaql_migrations(version, applied_at) values('{}',{})",table_name, chrono::now_format(3))
    );
    if let Err(e) = conn.execute(stmt1).await{
        return code::sys(e.to_string())
    };
    if let Err(e) = conn.execute(stmt2).await{
        return code::sys(e.to_string())
    };
    code::send_ok( conn.commit().await )
}
pub async fn create_migration(Json(body):Json<JsonValue>) -> Result<Json<JsonValue>, Json<JsonValue>>{
    let dbname = query::str(&body,"database")?;
    let conn = connect_db(&dbname).await?;
    let rs = table_exist(&conn, &dbname, "seaql_migrations").await?;
    if rs {
        return code::err("【seaql_migrations】表已存在");
    }
    let stmt = Statement::from_string(
        DbBackend::MySql, 
        r#"
            create table if not exists seaql_migrations (
                version varchar(255) not null comment '表名',
                applied_at bigint not null,
                top tinyint(1) comment '置顶',
                clear tinyint(1) comment '是否清空表',
                create_time	timestamp default current_timestamp comment '创建时间'
            )
        "#
    );
    code::send_ok( conn.execute(stmt).await )
}
pub async fn sort_id( Json(body):Json<JsonValue> ) -> Result<Json<JsonValue>, Json<JsonValue>>{
    let dbname = query::str(&body,"database")?;
    let table_name = query::str(&body,"id")?;
    let conn = connect_db(&dbname).await?;
    
    let transaction = conn.transaction::<_, (), DbErr>(
        |txn|{
            Box::pin(async move {
                let stmt = Statement::from_string(
                    DbBackend::MySql, 
                    &format!("alter table {} drop column id", table_name)
                );
               txn.execute(stmt).await?;
                let stmt = Statement::from_string(
                    DbBackend::MySql, 
                    &format!("alter table {} add column id int auto_increment primary key first", table_name)
                );
                txn.execute(stmt).await?;
                Ok(())
            })
        }
    ).await;
    code::send_ok(transaction)
}
pub async fn mod_table_name(Json(body):Json<JsonValue>) -> Result<Json<JsonValue>, Json<JsonValue>>{
    let dbname = query::str(&body,"database")?;
    let table_name = query::str(&body,"table_name")?;
    let name = query::str(&body,"name")?;
    let conn = connect_db(&dbname).await?;
    let stmt = Statement::from_string(
        DbBackend::MySql, 
    format!("alter table {} rename to {}", table_name, &name)
    );

    let _ = conn.execute(stmt).await;

    let stmt = Statement::from_string(
        DbBackend::MySql, 
        format!("update seaql_migrations set version='{}' where version='{}'", &name, &table_name)
    );

    let _ = conn.execute(stmt).await;

    // if let Err(e) = conn.execute(stmt).await{
    //     return code::sys(e.to_string())
    // };

    code::ok()
}
pub async fn intofile( Json(body):Json<JsonValue> ) -> Result<Json<JsonValue>, Json<JsonValue>>{
    let dbname = query::str(&body,"database")?;
    let conn = connect_db(&dbname).await?;

    let data = query::vec(&body, "data")?;
    let table_name = query::str(&body, "table_name")?;

    let mut col_str = String::new();

    for (k, _v) in data[0].as_object().unwrap(){
        let m = k.to_owned();
        let x = m + ",";
        col_str.push_str(&x);
    }
    let col_str_last = col_str.strip_suffix(",").unwrap();
    let mut value_str = String::new();

    for value in data {
        let mut str = String::new();
        for (_k, v) in value.as_object().unwrap(){
            let m = v.to_string() + ",";
            str.push_str(&m);
        }
        let s = str.strip_suffix(",").unwrap();
        let m = "(".to_owned() + &s + "),";
        value_str.push_str(&m);
    }
    let value_str_last = value_str.strip_suffix(",").unwrap();

    let sql = format!(" insert into {} ({}) values {}", table_name, col_str_last, value_str_last);

    let stmt = Statement::from_string(
        DbBackend::MySql, 
        sql
    );
    send_sql_ok(&conn, conn.execute(stmt).await).await
}
pub async fn del(Json(body):Json<JsonValue>) -> Result<Json<JsonValue>, Json<JsonValue>>{
    let dbname = query::str(&body,"database")?;
    let conn = connect_db(&dbname).await?;
    let conn = bus::begin(conn).await?;
    let table_name = query::str(&body,"table_name")?;
    // 删除表
    let stmt1 = Statement::from_string(
        DbBackend::MySql, 
        format!("drop table {}", table_name)
    );
    // 删除记录
    let stmt2 = Statement::from_string(
        DbBackend::MySql, 
        format!("delete from seaql_migrations where version='{}'", table_name )
    );
    _ = conn.execute(stmt1).await;
    _ = conn.execute(stmt2).await;
    code::send_ok(conn.commit().await)
}
pub async fn backups_csv(Query(param): Query<JsonValue>) -> Result<Json<JsonValue>, Json<JsonValue>>{
    let dbname = query::str(&param,"database")?;
    let conn = connect_db(&dbname).await?;
    let table_name: String = query::str(&param,"table_name")?;
    let sql = format!(r#"
        select * from {}
        into outfile 'E:/csv/{}.csv'
        character set utf8mb4
        fields terminated by ','
        enclosed by '"'
        optionally enclosed by '"'
        lines terminated by '\n'
    "#, table_name, table_name.to_owned() + "_" + &now_format(3));
    let stmt = Statement::from_string(
        DbBackend::MySql, 
        sql
    );
    code::send_ok( conn.execute(stmt).await )
}
pub async fn backups_excel(Query(param): Query<JsonValue>) -> Result<Json<JsonValue>, Json<JsonValue>>{
    let dbname = query::str(&param,"database")?;
    let table_name = query::str(&param,"table_name")?;
    let conn = connect_db(&dbname).await?;
    let sql = format!(r#"
        select * from {} into outfile 'E:/csv/{}.xls'
        character set gbk
    "#, table_name, table_name.to_owned() + "_" + &now_format(3));
    let stmt = Statement::from_string(
        DbBackend::MySql, 
        sql
    );
    code::send_ok( conn.execute(stmt).await )
}
pub async fn add_table_data(Json(body):Json<JsonValue>) -> Result<Json<JsonValue>, Json<JsonValue>>{
    let dbname = query::str(&body,"database")?;
    let table_name = query::str(&body,"table_name")?;
    let names = query::vec_str(&body,"names")?;
    let names = names.join(",");
    let values = query::vec_str(&body,"values")?;
    let values = values.join(",");
    let conn = connect_db(&dbname).await?;
    let stmt = Statement::from_string(
        DbBackend::MySql, 
        format!("insert into {} ({}) values({})", table_name, names, values)
    );
    code::send_ok( conn.execute(stmt).await )
}
pub async fn update_table_data(Json(body):Json<JsonValue>) -> Result<Json<JsonValue>, Json<JsonValue>>{
    let dbname = query::str(&body,"database")?;
    let table_name = query::str(&body,"table_name")?;
    let id = query::i32(&body,"id")?;
    let conn = connect_db(&dbname).await?;
    let update = query::vec_str(&body,"update")?;
    let update = update.join(",");
    let stmt = Statement::from_string(
        DbBackend::MySql, 
        format!("update {} set {} where id={}", table_name, update, id)
    );
    code::send_ok( conn.execute(stmt).await )
}
pub async fn del_table_data(Json(body):Json<JsonValue>) -> Result<Json<JsonValue>, Json<JsonValue>>{
    let dbname = query::str(&body,"database")?;
    let table_name = query::str(&body,"table_name")?;
    let id = query::vec_str_vec_num(&body,"id")?;

    let sql = if id.is_empty() {
        let id = query::vec_str(&body,"id")?;
        format!("delete from {} where find_in_set(id, '{}')", table_name, id.join(","))
    }else{
        format!("delete from {} where id in({})", table_name, id.join(","))
    };
   
    let conn = connect_db(&dbname).await?;
	
    let stmt = Statement::from_string( DbBackend::MySql, sql );
    code::send_ok( conn.execute(stmt).await )
}
// 清空，同时重置自增计数器
pub async fn truncate_table(Json(body):Json<JsonValue>) -> Result<Json<JsonValue>, Json<JsonValue>>{
    let dbname = query::str(&body,"database")?;
    let table_name = query::vec_str(&body,"table_name")?;
    let conn = connect_db(&dbname).await?;
    let conn = bus::begin(conn).await?;
    let mut err = false;
    for v in table_name {
        let stmt = Statement::from_string(DbBackend::MySql, format!("truncate table {}", &v) );
        if let Err(_) = conn.execute(stmt).await {
            err = true;
            break;
        }
    }
    if err {
        code::err("清空出错")
    }else{
        code::send_ok(conn.commit().await)
    }
}
// 删除表中的所有数据，但不删除表结构
pub async fn delete_table(Json(body):Json<JsonValue>) -> Result<Json<JsonValue>, Json<JsonValue>>{
    let dbname = query::str(&body,"database")?;
    let table_name = query::vec_str(&body,"table_name")?;
    let table_name = table_name.join(",");
    let conn = connect_db(&dbname).await?;
    let sql = format!("delete from {}", table_name);
    let stmt = Statement::from_string(
        DbBackend::MySql, 
        sql
    );
    code::send_ok( conn.execute(stmt).await )
}
// 修改表注释
pub async fn mod_table_comment(Json(body):Json<JsonValue>) -> Result<Json<JsonValue>, Json<JsonValue>>{
    let dbname = query::str(&body,"database")?;
    let table_name = query::str(&body,"table_name")?;
    let name = query::str(&body,"name")?;

    let conn = connect_db(&dbname).await?;
    let sql = format!("alter table {} comment='{}'", table_name, name);
    let stmt = Statement::from_string(
        DbBackend::MySql, 
        sql
    );
    code::send_ok( conn.execute(stmt).await )
}